package com.ajr.core.oscommerce;

import java.io.Serializable;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Iterator;
import java.util.List;

import org.apache.log4j.Logger;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.Transaction;

import com.ajr.core.Settings;
import com.ajr.core.db.CustomerToCustomer;
import com.ajr.core.db.Languages;
import com.ajr.core.db.OrderToOrder;
import com.ajr.core.db.Table;

public class OSCommerceAction {
	static Logger logger = Logger.getLogger(OSCommerceAction.class);

	
	public static boolean  insertCustomerMapInsert(String oscommerceId,String spcsId) {
		logger.info("inserting jinterbroker customer "+oscommerceId+":"+spcsId);
		
		Session session = Settings.getInstance().getSessionFactory();

		try {
			
			String querySql = " from CustomerToCustomer where jinterbroker_oscommerce_id = "+oscommerceId;
			// insurance.lngInsuranceId='1'
			Query query = session.createQuery(querySql);
			int size = query.list().size();
			if(size == 0) {
				Transaction t = session.beginTransaction();
				logger.info("Customer not exists in mapping table so inserting "+oscommerceId+":"+spcsId);
				CustomerToCustomer customerTOCustomer = new CustomerToCustomer();
				customerTOCustomer.setJinterbroker_oscommerce_id(oscommerceId);
				customerTOCustomer.setJinterbroker_spcs_number(spcsId);
				session.save(customerTOCustomer);
				t.commit();
//				session.close();
			}else {
				logger.info("Not inserting/updating jinterbroker_map_customer_to_customer Customer id already exits in db "+oscommerceId);
			}

			return true;
		} catch (Exception e) {
			logger.error("Error while updating jinterborker tables", e);
			// TODO: handle exception

		} 

		// System.out.println("properties "+properties);
	
		return false;
	}
	
	public static boolean  insertCustomerMapInsert1(String oscommerceId,String spcsId) {


		String url = Settings.getInstance().getConnectionURL();
		String user = Settings.getInstance().getUserName();
		String password = Settings.getInstance().getPassword();
		String driver = Settings.getInstance().getDriverClassName();

		Statement stmt1 = null;
		java.sql.Connection c = null;

		try {
			Class.forName(driver);
			DriverManager.getConnection(url, user, password);
			// Session session = sessionFactory.openSession();
			// java.sql.Connection c =
			// sessionFactory.openSession().connection();
			c = DriverManager.getConnection(url, user, password);
			
			String querySql = "select * from jinterbroker_map_customer_to_customer where jinterbroker_oscommerce_id = "+oscommerceId;
			stmt1 = c.createStatement();
			ResultSet rs = stmt1.executeQuery(querySql);
			
			if(rs.next()) {
				logger.info("Not inserting/updating jinterbroker_map_customer_to_customer Customer id already exits in db "+oscommerceId);
				
			}else {
				String orderDetailSQL = "insert jinterbroker_map_customer_to_customer(jinterbroker_oscommerce_id,jinterbroker_spcs_number) values('"+oscommerceId+"','"+spcsId+"')";
				stmt1.executeUpdate(orderDetailSQL);
			}
			rs.close();

			return true;
		} catch (Exception e) {
			logger.error("Error while updating jinterborker tables", e);
			// TODO: handle exception

		} finally {
			try {
				stmt1.close();
				c.close();
			} catch (Exception e2) {
				logger.error("Error while closing connection", e2);
			}
		}

		// System.out.println("properties "+properties);
	
		return false;
	}
	
	public static String  getVismaCustomerNumber(String oscommerceId) {
		Session sesison = Settings.getInstance().getSessionFactory();
		try {
			// sessionFactory.openSession().connection();
			String querySql = " from CustomerToCustomer where jinterbroker_oscommerce_id = '"+oscommerceId+"'";
			
			Query query = sesison.createQuery(querySql);
			List list = query.list();
			if(list.size() > 0) {
				System.out.println("List number "+list.get(0));
//				spcsCustoemr = rs.getString("jinterbroker_spcs_number");
			}
			String spcsCustoemr = null;

			return spcsCustoemr;
		} catch (Exception e) {
			logger.error("Error reading visma customer id from oscommerce", e);
		}

		// System.out.println("properties "+properties);
	
		return null;
	}

	
	public static String  getVismaCustomerNumber1(String oscommerceId) {


		String url = Settings.getInstance().getConnectionURL();
		String user = Settings.getInstance().getUserName();
		String password = Settings.getInstance().getPassword();
		String driver = Settings.getInstance().getDriverClassName();

		Statement stmt1 = null;
		ResultSet rs1 = null;
		java.sql.Connection c = null;

		try {
			Class.forName(driver);
			DriverManager.getConnection(url, user, password);
			// Session session = sessionFactory.openSession();
			// java.sql.Connection c =
			// sessionFactory.openSession().connection();
			c = DriverManager.getConnection(url, user, password);
			
			String querySql = "select * from jinterbroker_map_customer_to_customer where jinterbroker_oscommerce_id = '"+oscommerceId+"'";
			stmt1 = c.createStatement();
			ResultSet rs = stmt1.executeQuery(querySql);
			
			String spcsCustoemr = null;
			if(rs.next()) {
				spcsCustoemr = rs.getString("jinterbroker_spcs_number");
			}

			return spcsCustoemr;
		} catch (Exception e) {
			logger.error("Error reading visma customer id from oscommerce", e);


		} finally {
			try {
				rs1.close();
				stmt1.close();
				c.close();
			} catch (Exception e2) {
				logger.error("Error closing connection", e2);
			}
		}

		// System.out.println("properties "+properties);
	
		return null;
	}
	
	
	public static String  getLanguageCode1(String code) {


		String url = Settings.getInstance().getConnectionURL();
		String user = Settings.getInstance().getUserName();
		String password = Settings.getInstance().getPassword();
		String driver = Settings.getInstance().getDriverClassName();

		Statement stmt1 = null;
		java.sql.Connection c = null;

		try {
			Class.forName(driver);
			DriverManager.getConnection(url, user, password);
			// Session session = sessionFactory.openSession();
			// java.sql.Connection c =
			// sessionFactory.openSession().connection();
			c = DriverManager.getConnection(url, user, password);
			
			String querySql = "select code from languages where languages_id = "+code;
			stmt1 = c.createStatement();
			ResultSet rs = stmt1.executeQuery(querySql);
			
			String lcode = null;
			if(rs.next()) {
				lcode = rs.getString("code");
			}else {
				lcode = code;
			}
			rs.close();

			return lcode;
		} catch (Exception e) {
			logger.error("Error reading oscommerce language code", e);

		} finally {
			try {
				stmt1.close();
				c.close();
			} catch (Exception e2) {
				logger.error("Error closing connection", e2);
			}
		}

		// System.out.println("properties "+properties);
	
		return code;
	}

	
	public static String  getLanguageCode(String code) {


		Session session = Settings.getInstance().getSessionFactory();

		try {
			String querySql = " from Languages where languages_id = '"+code+"'";
			Query query = session.createQuery(querySql);
			Iterator ite = query.iterate();
			if(ite.hasNext()) {
				Languages languages = (Languages)ite.next();
				return languages.getCode();
			}
		} catch (Exception e) {
			logger.error("Error reading oscommerce language code", e);

		} 

		// System.out.println("properties "+properties);
	
		return code;
	}

	// select * from INFORMATION_SCHEMA.TABLES;

	public static boolean  isCustomerGroupExists() {
		return isTableExists("customers_groups");
	}
	
	public static boolean  isTableExists(String tableName) {
		Session session = Settings.getInstance().getSessionFactory();
		try {
			// Session session = sessionFactory.openSession();
			// java.sql.Connection c =
			
			String querySql = "select TABLE_SCHEMA,TABLE_NAME FROM INFORMATION_SCHEMA.TABLES tbs where UPPER(TABLE_NAME) = '"+tableName.toUpperCase()+"'";
			Query query = session.createSQLQuery(querySql);//.addEntity("tbs", Table.class);
			List list = query.list();
			if(list.size() > 0) {
				return true;
			}
			return false;
		} catch (Exception e) {
			logger.error("Error while finding table existence", e);

		}
	
		return false;
	}


	public static boolean  isTableExists1(String tableName) {


		String url = Settings.getInstance().getConnectionURL();
		String user = Settings.getInstance().getUserName();
		String password = Settings.getInstance().getPassword();
		String driver = Settings.getInstance().getDriverClassName();

		Statement stmt1 = null;
		java.sql.Connection c = null;

		try {
			Class.forName(driver);
			DriverManager.getConnection(url, user, password);
			// Session session = sessionFactory.openSession();
			// java.sql.Connection c =
			// sessionFactory.openSession().connection();
			c = DriverManager.getConnection(url, user, password);
			
			String querySql = "select * FROM INFORMATION_SCHEMA.TABLES where UPPER(TABLE_NAME) = '"+tableName.toUpperCase()+"'";
			stmt1 = c.createStatement();
			ResultSet rs = stmt1.executeQuery(querySql);
			
			String lcode = null;
			if(rs.next()) {
				return true;
			}
			rs.close();

			return false;
		} catch (Exception e) {
			logger.error("Error while finding table existence", e);

		} finally {
			try {
				stmt1.close();
				c.close();
			} catch (Exception e2) {
				logger.error("Error closing connections", e2);
			}
		}

		// System.out.println("properties "+properties);
	
		return false;
	}

	
	public static boolean insertOrderMapInsert1(String oscommerceId,String spcsId) {


		String url = Settings.getInstance().getConnectionURL();
		String user = Settings.getInstance().getUserName();
		String password = Settings.getInstance().getPassword();
		String driver = Settings.getInstance().getDriverClassName();

		Statement stmt1 = null;
		java.sql.Connection c = null;

		try {
			Class.forName(driver);
			DriverManager.getConnection(url, user, password);
			// Session session = sessionFactory.openSession();
			// java.sql.Connection c =
			// sessionFactory.openSession().connection();
			c = DriverManager.getConnection(url, user, password);
			
			String querySql = "select * from jinterbroker_map_order_to_order where jinterbroker_oscommerce_id = "+oscommerceId;
			stmt1 = c.createStatement();
			ResultSet rs = stmt1.executeQuery(querySql);
			
			if(rs.next()) {
				logger.info("Not inserting/updating jinterbroker_map_order_to_order Order id already exits in db "+oscommerceId);

			}else {
				String orderDetailSQL = "insert jinterbroker_map_order_to_order(jinterbroker_oscommerce_id,jinterbroker_spcs_number) values('"+oscommerceId+"','"+spcsId+"')";
				stmt1.executeUpdate(orderDetailSQL);
			}
			rs.close();
			return true;
		} catch (Exception e) {
			logger.error("Error inserting order in oscommerce", e);

		} finally {
			try {
				stmt1.close();
				c.close();
			} catch (Exception e2) {
				logger.error("Error closing connection", e2);
			}
		}
		return false;
	}
	
	
	public static boolean insertOrderMapInsert(String oscommerceId,String spcsId) {
		logger.info("inserting jinterbroker order "+oscommerceId+":"+spcsId);

		Session session = Settings.getInstance().getSessionFactory();
		try {
			
			String querySql = " from OrderToOrder where jinterbroker_oscommerce_id = "+oscommerceId;
			Query query = session.createQuery(querySql);
			
			List list = query.list();
			if(list.size() > 0) {
				logger.info("Not inserting/updating jinterbroker_map_order_to_order Order id already exits in db "+oscommerceId);

			}else {
				logger.info("Order not exists in mapping table so inserting "+oscommerceId+":"+spcsId);
				Transaction t = session.beginTransaction();

				OrderToOrder orderToOrder = new OrderToOrder();
				orderToOrder.setJinterbroker_oscommerce_id(oscommerceId);
				orderToOrder.setJinterbroker_spcs_number(spcsId);
				Serializable s = session.save(orderToOrder);
				System.out.println("Serialiazable reutur "+s);
				t.commit();
//				session.close();
			}
			return true;
		} catch (Exception e) {
			logger.error("Error inserting order in oscommerce", e);

		} 
		return false;
	}

	public static boolean isOrderExported(String orderId) {
		Session sesison = Settings.getInstance().getSessionFactory();
		try {
			// sessionFactory.openSession().connection();
			String querySql = " from OrderToOrder where jinterbroker_oscommerce_id = "+orderId;
			
			Query query = sesison.createQuery(querySql);
			List list = query.list();
			if(list.size() > 0) {
				System.out.println("List number "+list.get(0));
//				spcsCustoemr = rs.getString("jinterbroker_spcs_number");
				return true;
			}

			return false;
		} catch (Exception e) {
			logger.error("Error reading visma customer id from oscommerce", e);
		}

		// System.out.println("properties "+properties);
	
		return false;
	}


}
